CodingPro

Structured Query Language (SQL) is a standard language used to manage and manipulate relational databases. Whether you're a beginner or an aspiring database administrator, understanding SQL fundamentals is crucial. In this guide, we'll explore SQL basics, including database concepts, data types, and key SQL statements.

1. Database Concepts

A database is an organized collection of data. SQL databases are structured into tables, which contain rows (records) and columns (fields). Let's break these down:

• Tables: The fundamental structure in an SQL database. Each table holds data related to a specific topic.
• Rows (Records): Each row in a table represents a single entity or instance.
• Columns (Fields): Define the type of data stored in a table.

Example of a Simple Table: Customers

2. Data Types in SQL

SQL provides various data types to define the nature of data stored in a table. Common data types include:

• INTEGER: Whole numbers (e.g., ID INT)
• VARCHAR(n): Variable-length string (e.g., Name VARCHAR(255))
• TEXT: Large text fields (e.g., Description TEXT)
• DATE: Stores date values (e.g., BirthDate DATE)
• BOOLEAN: True/False values (e.g., IsActive BOOLEAN)
• DECIMAL(p,s): Stores decimal numbers with precision (e.g., Price DECIMAL(10,2))

3. Creating a Database

To start working with SQL, you need to create a database. Use the following SQL command:

CREATE DATABASE my_database;

To use the newly created database:

USE my_database;

4. Creating Tables

Once the database is set up, create tables to store data. The CREATE TABLE statement is used as follows:

CREATE TABLE Customers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Age INT
);

This table includes:

• ID as a primary key (auto-increments for uniqueness)
• Name and Email as string fields
• Email marked as UNIQUE to prevent duplicates
• Age as an integer

5. Inserting Data into Tables

To add records to a table, use the INSERT INTO statement:

INSERT INTO Customers (Name, Email, Age) VALUES ('John Doe', 'john@example.com', 30);
INSERT INTO Customers (Name, Email, Age) VALUES ('Jane Doe', 'jane@example.com', 25);

6. Retrieving Data (SELECT Statement)

The SELECT statement fetches data from a table:

SELECT * FROM Customers;

To retrieve specific columns:

SELECT Name, Email FROM Customers;

To filter data:

SELECT * FROM Customers WHERE Age > 25;

7. Updating and Deleting Data

Updating Data

To modify existing records, use the UPDATE statement:

UPDATE Customers SET Age = 35 WHERE Name = 'John Doe';

Deleting Data

To remove records from a table, use the DELETE statement:

DELETE FROM Customers WHERE Name = 'Jane Doe';

To delete all records but keep the table structure:

DELETE FROM Customers;

To remove the entire table:

DROP TABLE Customers;

Conclusion

SQL is a powerful tool for managing relational databases. Understanding database concepts, data types, and fundamental SQL commands like CREATE, INSERT, SELECT, UPDATE, and DELETE will set the foundation for advanced database operations. In the next steps, we’ll dive deeper into SQL joins, indexing, stored procedures, and performance tuning.

Happy coding!